﻿Imports System.Data.OleDb
Public Class frmThongKeSoLuongTon
    Private RowEditIndex As Integer
    Private Sub frmThongKeSoLuongTon_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            LoaiSanPham_Load()
            If dtpNgayHopDong.Value.ToShortDateString = Now.Date Then
                Dim current As String = dtpNgayHopDong.Value.ToShortDateString
                formatDate2VN(current)
                lblNgayTonKho.Text = "( " & current & " )"
                SoLuongTonKho_Load_ByDateNow()
            Else
                SoLuongTonKho_Load_ByDate()
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#Region "Load loại sản phầm và sản phẩm"
    Private Sub LoaiSanPham_Load()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsLoaiSanPham As New DataSet
            Dim dtLoaiSanPham As New DataTable
            Dim query As String = "Select IDLoai,TenLoai From LoaiSanPham Union Select IdTatCa As IDLoai,TatCa As TenLoai From TatCa"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsLoaiSanPham)
            dbConn.Close()
            dtLoaiSanPham = dsLoaiSanPham.Tables(0)
            cbxLoaiSanPham.DataSource = dtLoaiSanPham
            cbxLoaiSanPham.ValueMember = "IdLoai"
            cbxLoaiSanPham.DisplayMember = "TenLoai"
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
#Region "Load Số lượng tồn kho"
    Public Sub SoLuongTonKho_Load_ByDateNow()
        Try
            Cursor.Current = Cursors.WaitCursor
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim query As String = ""
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dtSoLuongTonKho2Column As DataTable = Create_SoLuongTonKho2Column()
            'grouping
            Dim dtMaLoai As New DataTable
            If cbxLoaiSanPham.SelectedValue = 0 Then
                query = "Select Distinct IDLoai,MaLoai,TenLoai From LoaiSanPham Order By MaLoai ASC"
            Else
                query = "Select Distinct IDLoai,MaLoai,TenLoai From LoaiSanPham Where IDLoai=" & cbxLoaiSanPham.SelectedValue & " Order By MaLoai ASC"
            End If

            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtMaLoai)
            For j As Integer = 0 To dtMaLoai.Rows.Count - 1
                'grouping detail
                Dim dtSoLuongTonKho As New DataTable
                If txtTenSanPhamSearch.Text = "" Then
                    query = "Select LoaiSanPham.IdLoai,TenLoai,IDSanPham,MaSanPham,TenSanPham,SoLuongTon From SanPham Left Outer Join LoaiSanPham On SanPham.IDLoai=LoaiSanPham.IDLoai Where SanPham.TheoDoiTon=1 And SanPham.Xoa=0 And SanPham.DVTSanPhamId IS NOT NULL And LoaiSanPham.IdLoai=" & dtMaLoai.Rows(j)("IdLoai") & " Order By MaSanPham ASC"
                Else
                    query = "Select LoaiSanPham.IdLoai,TenLoai,IDSanPham,MaSanPham,TenSanPham,SoLuongTon From SanPham Left Outer Join LoaiSanPham On SanPham.IDLoai=LoaiSanPham.IDLoai Where SanPham.TheoDoiTon=1 And SanPham.Xoa=0 And LoaiSanPham.IdLoai=" & dtMaLoai.Rows(j)("IdLoai") & " And SanPham.DVTSanPhamId IS NOT NULL And SanPham.Xoa=0 And MaSanPham Like N'%" & txtTenSanPhamSearch.Text & "%' Order By MaSanPham ASC"
                End If
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSoLuongTonKho)

                'grouping title
                If dtSoLuongTonKho.Rows.Count - 1 > 0 Then
                    Dim newRow As DataRow
                    newRow = dtSoLuongTonKho2Column.NewRow
                    newRow.Item("IDLoai") = dtMaLoai.Rows(j)("IDLoai")
                    newRow.Item("TenLoai") = dtMaLoai.Rows(j)("TenLoai")
                    newRow.Item("IDSanPham") = ""
                    newRow.Item("MaSanPham") = ""
                    newRow.Item("TenSanPham") = ""
                    newRow.Item("SoLuongTon") = ""
                    newRow.Item("TenLoai1") = ""
                    newRow.Item("IDSanPham1") = ""
                    newRow.Item("MaSanPham1") = ""
                    newRow.Item("TenSanPham1") = ""
                    newRow.Item("SoLuongTon1") = ""
                    dtSoLuongTonKho2Column.Rows.Add(newRow)
                End If
                Dim i As Integer = 0
                'Chia Ra Lam Doi
                Dim ToTal As Integer = dtSoLuongTonKho.Rows.Count - 1
                Dim Part1 As Integer = ToTal / 2
                If ToTal = -1 Then

                ElseIf ToTal = 0 Then
                    Dim newRow2 As DataRow
                    newRow2 = dtSoLuongTonKho2Column.NewRow
                    newRow2.Item("IDLoai") = dtSoLuongTonKho.Rows(0)("IdLoai")
                    newRow2.Item("TenLoai") = ""
                    newRow2.Item("IDSanPham") = dtSoLuongTonKho.Rows(0)("IDSanPham")
                    newRow2.Item("MaSanPham") = dtSoLuongTonKho.Rows(0)("MaSanPham")
                    newRow2.Item("TenSanPham") = dtSoLuongTonKho.Rows(0)("TenSanPham")
                    newRow2.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(0)("SoLuongTon"), "0.00")
                    newRow2.Item("TenLoai1") = ""
                    newRow2.Item("IDSanPham1") = ""
                    newRow2.Item("MaSanPham1") = ""
                    newRow2.Item("TenSanPham1") = ""
                    newRow2.Item("SoLuongTon1") = ""
                    dtSoLuongTonKho2Column.Rows.Add(newRow2)
                ElseIf ToTal = 1 Then
                    Dim newRow2 As DataRow
                    newRow2 = dtSoLuongTonKho2Column.NewRow
                    newRow2.Item("IDLoai") = dtSoLuongTonKho.Rows(0)("IdLoai")
                    newRow2.Item("TenLoai") = ""
                    newRow2.Item("IDSanPham") = dtSoLuongTonKho.Rows(0)("IDSanPham")
                    newRow2.Item("MaSanPham") = dtSoLuongTonKho.Rows(0)("MaSanPham")
                    newRow2.Item("TenSanPham") = dtSoLuongTonKho.Rows(0)("TenSanPham")
                    newRow2.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(0)("SoLuongTon"), "0.00")
                    newRow2.Item("TenLoai1") = ""
                    newRow2.Item("IDSanPham1") = dtSoLuongTonKho.Rows(1)("IDSanPham")
                    newRow2.Item("MaSanPham1") = dtSoLuongTonKho.Rows(1)("MaSanPham")
                    newRow2.Item("TenSanPham1") = dtSoLuongTonKho.Rows(1)("TenSanPham")
                    newRow2.Item("SoLuongTon1") = Format(dtSoLuongTonKho.Rows(1)("SoLuongTon"), "0.00")
                    dtSoLuongTonKho2Column.Rows.Add(newRow2)
                Else
                    While (i <= Part1)
                        If (i + Part1 + 1) > dtSoLuongTonKho.Rows.Count - 1 Then
                            Dim newRow1 As DataRow
                            newRow1 = dtSoLuongTonKho2Column.NewRow
                            newRow1.Item("IDLoai") = dtSoLuongTonKho.Rows(i)("IdLoai")
                            newRow1.Item("TenLoai") = ""
                            newRow1.Item("IDSanPham") = dtSoLuongTonKho.Rows(i)("IDSanPham")
                            newRow1.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
                            newRow1.Item("TenSanPham") = dtSoLuongTonKho.Rows(i)("TenSanPham")
                            newRow1.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(i)("SoLuongTon"), "0.00")
                            newRow1.Item("TenLoai1") = ""
                            newRow1.Item("MaSanPham1") = ""
                            newRow1.Item("TenSanPham1") = ""
                            newRow1.Item("SoLuongTon1") = ""
                            dtSoLuongTonKho2Column.Rows.Add(newRow1)
                        Else
                            Dim newRow2 As DataRow
                            newRow2 = dtSoLuongTonKho2Column.NewRow
                            newRow2.Item("IDLoai") = dtSoLuongTonKho.Rows(i)("IdLoai")
                            newRow2.Item("TenLoai") = ""
                            newRow2.Item("IDSanPham") = dtSoLuongTonKho.Rows(i)("IDSanPham")
                            newRow2.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
                            newRow2.Item("TenSanPham") = dtSoLuongTonKho.Rows(i)("TenSanPham")
                            newRow2.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(i)("SoLuongTon"), "0.00")
                            newRow2.Item("TenLoai1") = ""
                            newRow2.Item("IDSanPham1") = dtSoLuongTonKho.Rows(i + Part1 + 1)("IDSanPham")
                            newRow2.Item("MaSanPham1") = dtSoLuongTonKho.Rows(i + Part1 + 1)("MaSanPham")
                            newRow2.Item("TenSanPham1") = dtSoLuongTonKho.Rows(i + Part1 + 1)("TenSanPham")
                            newRow2.Item("SoLuongTon1") = Format(dtSoLuongTonKho.Rows(i + Part1 + 1)("SoLuongTon"), "0.00")
                            dtSoLuongTonKho2Column.Rows.Add(newRow2)
                        End If
                        i = i + 1
                    End While
                End If
                
                'While i <= dtSoLuongTonKho.Rows.Count - 1
                '    If (i + 1) > dtSoLuongTonKho.Rows.Count - 1 Then
                '        Dim newRow1 As DataRow
                '        newRow1 = dtSoLuongTonKho2Column.NewRow
                '        newRow1.Item("IDLoai") = dtSoLuongTonKho.Rows(i)("IdLoai")
                '        newRow1.Item("TenLoai") = ""
                '        newRow1.Item("IDSanPham") = dtSoLuongTonKho.Rows(i)("IDSanPham")
                '        newRow1.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
                '        newRow1.Item("TenSanPham") = dtSoLuongTonKho.Rows(i)("TenSanPham")
                '        newRow1.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(i)("SoLuongTon"), "0.00")
                '        newRow1.Item("TenLoai1") = ""
                '        newRow1.Item("MaSanPham1") = ""
                '        newRow1.Item("TenSanPham1") = ""
                '        newRow1.Item("SoLuongTon1") = ""
                '        dtSoLuongTonKho2Column.Rows.Add(newRow1)
                '    Else
                '        Dim newRow2 As DataRow
                '        newRow2 = dtSoLuongTonKho2Column.NewRow
                '        newRow2.Item("IDLoai") = dtSoLuongTonKho.Rows(i)("IdLoai")
                '        newRow2.Item("TenLoai") = ""
                '        newRow2.Item("IDSanPham") = dtSoLuongTonKho.Rows(i)("IDSanPham")
                '        newRow2.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
                '        newRow2.Item("TenSanPham") = dtSoLuongTonKho.Rows(i)("TenSanPham")
                '        newRow2.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(i)("SoLuongTon"), "0.00")
                '        newRow2.Item("TenLoai1") = ""
                '        newRow2.Item("IDSanPham1") = dtSoLuongTonKho.Rows(i + 1)("IDSanPham")
                '        newRow2.Item("MaSanPham1") = dtSoLuongTonKho.Rows(i + 1)("MaSanPham")
                '        newRow2.Item("TenSanPham1") = dtSoLuongTonKho.Rows(i + 1)("TenSanPham")
                '        newRow2.Item("SoLuongTon1") = Format(dtSoLuongTonKho.Rows(i + 1)("SoLuongTon"), "0.00")
                '        dtSoLuongTonKho2Column.Rows.Add(newRow2)
                '    End If
                '    i = i + 2
                'End While
            Next
            dbConn.Close()
            'bind data
            Grid1.ColumnsCount = 9
            Grid1.Rows.Clear()
            'tạo header 
            Grid1.FixedRows = 1
            Grid1.Rows.Insert(0)
            Grid1(0, 0) = New SourceGrid.Cells.ColumnHeader("Loại sản phẩm")
            Grid1(0, 0).Column.Width = 0

            Grid1(0, 1) = New SourceGrid.Cells.ColumnHeader("IDSanPham")
            Grid1(0, 1).Column.Width = 0

            Grid1(0, 2) = New SourceGrid.Cells.ColumnHeader("Mã sản phẩm")
            Grid1(0, 2).Column.Width = 100

            Grid1(0, 3) = New SourceGrid.Cells.ColumnHeader("Tên sản phẩm")
            Grid1(0, 3).Column.Width = 260

            Grid1(0, 4) = New SourceGrid.Cells.ColumnHeader("Tồn")
            Grid1(0, 4).Column.Width = 80

            Grid1(0, 5) = New SourceGrid.Cells.ColumnHeader("IDSanPham1")
            Grid1(0, 5).Column.Width = 0

            Grid1(0, 6) = New SourceGrid.Cells.ColumnHeader("Mã sản phẩm")
            Grid1(0, 6).Column.Width = 80

            Grid1(0, 7) = New SourceGrid.Cells.ColumnHeader("Tên sản phầm")
            Grid1(0, 7).Column.Width = 260

            Grid1(0, 8) = New SourceGrid.Cells.ColumnHeader("Tồn")
            Grid1(0, 8).Column.Width = 80
            'tạo detail 
            Dim rowCOLSPAN As New SourceGrid.Cells.Views.Cell
            Dim fontBold As New Font(Grid1.Font, FontStyle.Bold)
            rowCOLSPAN.BackColor = Color.FromArgb(192, 255, 192)
            rowCOLSPAN.Font = fontBold
            Dim cellNumberStyle As New SourceGrid.Cells.Views.Cell
            cellNumberStyle.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight

            Dim cellNumberStyleAm As New SourceGrid.Cells.Views.Cell
            cellNumberStyleAm.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
            cellNumberStyleAm.BackColor = Color.Yellow

            Dim cellNumberStyleKhong As New SourceGrid.Cells.Views.Cell
            cellNumberStyleKhong.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
            cellNumberStyleKhong.BackColor = Color.Red

            For i As Integer = 0 To dtSoLuongTonKho2Column.Rows.Count - 1
                Grid1.Rows.Insert(i + 1)
                If dtSoLuongTonKho2Column.Rows(i)("TenLoai").ToString <> "" Then
                    Grid1(i + 1, 0) = New SourceGrid.Cells.Cell("Loại sản phẩm: " & dtSoLuongTonKho2Column.Rows(i)("TenLoai").ToString)
                    Grid1(i + 1, 0).ColumnSpan = 9
                    Grid1(i + 1, 0).View = rowCOLSPAN
                Else
                    Grid1(i + 1, 0) = New SourceGrid.Cells.Cell()
                    Grid1(i + 1, 1) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("IDSanPham").ToString)
                    Grid1(i + 1, 2) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("MaSanPham").ToString)
                    Grid1(i + 1, 3) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("TenSanPham").ToString)
                    Grid1(i + 1, 4) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("SoLuongTon").ToString)
                    If IsDBNull(Grid1(i + 1, 4).Value) Or String.IsNullOrEmpty(Grid1(i + 1, 4).Value) Then
                        Grid1(i + 1, 4).View = cellNumberStyle
                    Else
                        If Single.Parse(Grid1(i + 1, 4).Value) = 0 Then
                            Grid1(i + 1, 4).View = cellNumberStyleKhong
                        ElseIf Single.Parse(Grid1(i + 1, 4).Value) < 0 Then
                            Grid1(i + 1, 4).View = cellNumberStyleAm
                        Else
                            Grid1(i + 1, 4).View = cellNumberStyle
                        End If
                    End If
                    Grid1(i + 1, 5) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("IDSanPham1").ToString)
                    Grid1(i + 1, 6) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("MaSanPham1").ToString)
                    Grid1(i + 1, 7) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("TenSanPham1").ToString)
                    Grid1(i + 1, 8) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("SoLuongTon1").ToString)
                    If IsDBNull(Grid1(i + 1, 8).Value) Or String.IsNullOrEmpty(Grid1(i + 1, 8).Value) Then
                        Grid1(i + 1, 8).View = cellNumberStyle
                    Else
                        If Single.Parse(Grid1(i + 1, 8).Value) = 0 Then
                            Grid1(i + 1, 8).View = cellNumberStyleKhong
                        ElseIf Single.Parse(Grid1(i + 1, 8).Value) < 0 Then
                            Grid1(i + 1, 8).View = cellNumberStyleAm
                        Else
                            Grid1(i + 1, 8).View = cellNumberStyle
                        End If
                    End If

                End If
            Next
            Cursor.Current = Cursors.Default
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub SoLuongTonKho_Load_ByDate()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim query As String = ""
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dtSoLuongTonKho2Column As DataTable = Create_SoLuongTonKho2Column()
            'grouping
            Dim dtMaLoai As New DataTable
            If cbxLoaiSanPham.SelectedValue = 0 Then
                query = "Select Distinct IDLoai,MaLoai,TenLoai From LoaiSanPham Order By MaLoai ASC"
            Else
                query = "Select Distinct IDLoai,MaLoai,TenLoai From LoaiSanPham Where IDLoai=" & cbxLoaiSanPham.SelectedValue & " Order By MaLoai ASC"
            End If

            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtMaLoai)
            For j As Integer = 0 To dtMaLoai.Rows.Count - 1
                'grouping detail
                Dim dtSoLuongTonKho As New DataTable
                If txtTenSanPhamSearch.Text = "" Then
                    query = "Select LoaiSanPham.IdLoai,TenLoai,IDSanPham,MaSanPham,TenSanPham,SoLuongTon From SanPham Left Outer Join LoaiSanPham On SanPham.IDLoai=LoaiSanPham.IDLoai Where LoaiSanPham.IdLoai=" & dtMaLoai.Rows(j)("IdLoai")
                Else
                    query = "Select LoaiSanPham.IdLoai,TenLoai,IDSanPham,MaSanPham,TenSanPham,SoLuongTon From SanPham Left Outer Join LoaiSanPham On SanPham.IDLoai=LoaiSanPham.IDLoai Where LoaiSanPham.IdLoai=" & dtMaLoai.Rows(j)("IdLoai") & " And MaSanPham Like N'%" & txtTenSanPhamSearch.Text & "%'"
                End If
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSoLuongTonKho)

                'grouping title
                If dtSoLuongTonKho.Rows.Count - 1 > 0 Then
                    Dim newRow As DataRow
                    newRow = dtSoLuongTonKho2Column.NewRow
                    newRow.Item("IDLoai") = dtMaLoai.Rows(j)("IDLoai")
                    newRow.Item("TenLoai") = dtMaLoai.Rows(j)("TenLoai")
                    newRow.Item("IDSanPham") = ""
                    newRow.Item("MaSanPham") = ""
                    newRow.Item("TenSanPham") = ""
                    newRow.Item("SoLuongTon") = ""
                    newRow.Item("TenLoai1") = ""
                    newRow.Item("IDSanPham1") = ""
                    newRow.Item("MaSanPham1") = ""
                    newRow.Item("TenSanPham1") = ""
                    newRow.Item("SoLuongTon1") = ""
                    dtSoLuongTonKho2Column.Rows.Add(newRow)
                End If
                Dim i As Integer = 0
                While i <= dtSoLuongTonKho.Rows.Count - 1
                    If (i + 1) > dtSoLuongTonKho.Rows.Count - 1 Then
                        Dim newRow1 As DataRow
                        newRow1 = dtSoLuongTonKho2Column.NewRow
                        newRow1.Item("IDLoai") = dtSoLuongTonKho.Rows(i)("IdLoai")
                        newRow1.Item("TenLoai") = ""
                        newRow1.Item("IDSanPham") = dtSoLuongTonKho.Rows(i)("IDSanPham")
                        newRow1.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
                        newRow1.Item("TenSanPham") = dtSoLuongTonKho.Rows(i)("TenSanPham")
                        newRow1.Item("SoLuongTon") = "0"
                        newRow1.Item("TenLoai1") = ""
                        newRow1.Item("MaSanPham1") = ""
                        newRow1.Item("TenSanPham1") = ""
                        newRow1.Item("SoLuongTon1") = ""
                        dtSoLuongTonKho2Column.Rows.Add(newRow1)
                    Else
                        Dim newRow2 As DataRow
                        newRow2 = dtSoLuongTonKho2Column.NewRow
                        newRow2.Item("IDLoai") = dtSoLuongTonKho.Rows(i)("IdLoai")
                        newRow2.Item("TenLoai") = ""
                        newRow2.Item("IDSanPham") = dtSoLuongTonKho.Rows(i)("IDSanPham")
                        newRow2.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
                        newRow2.Item("TenSanPham") = dtSoLuongTonKho.Rows(i)("TenSanPham")
                        newRow2.Item("SoLuongTon") = Single.Parse(dtSoLuongTonKho.Rows(i)("SoLuongTon")) + SoLuongDaXuat(dtSoLuongTonKho.Rows(i)("IDSanPham")) - SoLuongDaNhap(dtSoLuongTonKho.Rows(i)("IDSanPham"))
                        newRow2.Item("TenLoai1") = ""
                        newRow2.Item("IDSanPham1") = dtSoLuongTonKho.Rows(i + 1)("IDSanPham")
                        newRow2.Item("MaSanPham1") = dtSoLuongTonKho.Rows(i + 1)("MaSanPham")
                        newRow2.Item("TenSanPham1") = dtSoLuongTonKho.Rows(i + 1)("TenSanPham")
                        newRow2.Item("SoLuongTon1") = Single.Parse(dtSoLuongTonKho.Rows(i)("SoLuongTon")) + SoLuongDaXuat(dtSoLuongTonKho.Rows(i)("IDSanPham")) - SoLuongDaNhap(dtSoLuongTonKho.Rows(i)("IDSanPham"))
                        dtSoLuongTonKho2Column.Rows.Add(newRow2)
                    End If
                    i = i + 2
                End While
            Next
            dbConn.Close()
            'bind data
            Grid1.ColumnsCount = 9
            Grid1.Rows.Clear()
            'tạo header 
            Grid1.FixedRows = 1
            Grid1.Rows.Insert(0)
            Grid1(0, 0) = New SourceGrid.Cells.ColumnHeader("Loại sản phẩm")
            Grid1(0, 0).Column.Width = 0

            Grid1(0, 1) = New SourceGrid.Cells.ColumnHeader("IDSanPham")
            Grid1(0, 1).Column.Width = 0

            Grid1(0, 2) = New SourceGrid.Cells.ColumnHeader("Mã sản phẩm")
            Grid1(0, 2).Column.Width = 100

            Grid1(0, 3) = New SourceGrid.Cells.ColumnHeader("Tên sản phẩm")
            Grid1(0, 3).Column.Width = 260

            Grid1(0, 4) = New SourceGrid.Cells.ColumnHeader("Tồn")
            Grid1(0, 4).Column.Width = 80

            Grid1(0, 5) = New SourceGrid.Cells.ColumnHeader("IDSanPham1")
            Grid1(0, 5).Column.Width = 0

            Grid1(0, 6) = New SourceGrid.Cells.ColumnHeader("Mã sản phẩm")
            Grid1(0, 6).Column.Width = 80

            Grid1(0, 7) = New SourceGrid.Cells.ColumnHeader("Tên sản phầm")
            Grid1(0, 7).Column.Width = 260

            Grid1(0, 8) = New SourceGrid.Cells.ColumnHeader("Tồn")
            Grid1(0, 8).Column.Width = 80
            'tạo detail 
            Dim rowCOLSPAN As New SourceGrid.Cells.Views.Cell
            Dim fontBold As New Font(Grid1.Font, FontStyle.Bold)
            rowCOLSPAN.BackColor = Color.FromArgb(192, 255, 192)
            rowCOLSPAN.Font = fontBold
            Dim cellNumberStyle As New SourceGrid.Cells.Views.Cell
            cellNumberStyle.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
            For i As Integer = 0 To dtSoLuongTonKho2Column.Rows.Count - 1
                Grid1.Rows.Insert(i + 1)
                If dtSoLuongTonKho2Column.Rows(i)("TenLoai").ToString <> "" Then
                    Grid1(i + 1, 0) = New SourceGrid.Cells.Cell("Loại sản phẩm: " & dtSoLuongTonKho2Column.Rows(i)("TenLoai").ToString)
                    Grid1(i + 1, 0).ColumnSpan = 9
                    Grid1(i + 1, 0).View = rowCOLSPAN
                Else
                    Grid1(i + 1, 0) = New SourceGrid.Cells.Cell()
                    Grid1(i + 1, 1) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("IDSanPham").ToString)
                    Grid1(i + 1, 2) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("MaSanPham").ToString)
                    Grid1(i + 1, 3) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("TenSanPham").ToString)
                    Grid1(i + 1, 4) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("SoLuongTon").ToString)
                    Grid1(i + 1, 4).View = cellNumberStyle
                    Grid1(i + 1, 5) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("IDSanPham1").ToString)
                    Grid1(i + 1, 6) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("MaSanPham1").ToString)
                    Grid1(i + 1, 7) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("TenSanPham1").ToString)
                    Grid1(i + 1, 8) = New SourceGrid.Cells.Cell(dtSoLuongTonKho2Column.Rows(i)("SoLuongTon1").ToString)
                    Grid1(i + 1, 8).View = cellNumberStyle
                End If
            Next
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtTenSanPhamSearch_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTenSanPhamSearch.TextChanged
        Try
            If dtpNgayHopDong.Value.ToShortDateString = Now.Date Then
                SoLuongTonKho_Load_ByDateNow()
            Else
                SoLuongTonKho_Load_ByDate()
            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub cbxLoaiSanPham_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbxLoaiSanPham.SelectionChangeCommitted
        Try
            If dtpNgayHopDong.Value.ToShortDateString = Now.Date Then
                SoLuongTonKho_Load_ByDateNow()
            Else
                SoLuongTonKho_Load_ByDate()
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Protected Function Create_SoLuongTonKho2Column() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try

            Dim STT As DataColumn = New DataColumn("STT")
            STT.DataType = System.Type.GetType("System.String")
            STT.DefaultValue = ""
            dtTable.Columns.Add(STT)

            Dim IDLoai As DataColumn = New DataColumn("IDLoai")
            IDLoai.DataType = System.Type.GetType("System.String")
            IDLoai.DefaultValue = ""
            dtTable.Columns.Add(IDLoai)

            Dim TenLoai As DataColumn = New DataColumn("TenLoai")
            TenLoai.DataType = System.Type.GetType("System.String")
            TenLoai.DefaultValue = ""
            dtTable.Columns.Add(TenLoai)

            Dim IDSanPham As DataColumn = New DataColumn("IDSanPham")
            IDSanPham.DataType = System.Type.GetType("System.String")
            IDSanPham.DefaultValue = ""
            dtTable.Columns.Add(IDSanPham)

            Dim MaSanPham As DataColumn = New DataColumn("MaSanPham")
            MaSanPham.DataType = System.Type.GetType("System.String")
            MaSanPham.DefaultValue = ""
            dtTable.Columns.Add(MaSanPham)

            Dim TenSanPham As DataColumn = New DataColumn("TenSanPham")
            TenSanPham.DataType = System.Type.GetType("System.String")
            TenSanPham.DefaultValue = ""
            dtTable.Columns.Add(TenSanPham)

            Dim SoLuongTon As DataColumn = New DataColumn("SoLuongTon")
            SoLuongTon.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(SoLuongTon)

            Dim TenLoai1 As DataColumn = New DataColumn("TenLoai1")
            TenLoai1.DataType = System.Type.GetType("System.String")
            TenLoai1.DefaultValue = ""
            dtTable.Columns.Add(TenLoai1)

            Dim IDSanPham1 As DataColumn = New DataColumn("IDSanPham1")
            IDSanPham1.DataType = System.Type.GetType("System.String")
            IDSanPham1.DefaultValue = ""
            dtTable.Columns.Add(IDSanPham1)

            Dim MaSanPham1 As DataColumn = New DataColumn("MaSanPham1")
            MaSanPham.DataType = System.Type.GetType("System.String")
            MaSanPham.DefaultValue = ""
            dtTable.Columns.Add(MaSanPham1)

            Dim TenSanPham1 As DataColumn = New DataColumn("TenSanPham1")
            TenSanPham1.DataType = System.Type.GetType("System.String")
            TenSanPham1.DefaultValue = ""
            dtTable.Columns.Add(TenSanPham1)

            Dim SoLuongTon1 As DataColumn = New DataColumn("SoLuongTon1")
            SoLuongTon1.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(SoLuongTon1)
            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function
#Region "Tính số lượng tồn"
    Private Function SoLuongDaXuat(ByVal IdSanPham As Integer) As Single
        Try
            Dim sRet As Single = 0
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim query As String = ""
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dtDaXuat As New DataTable
            query = "Select SoLuong From ChiTietDonHangBan Where IDSanPham=" & IdSanPham & "And Ngay_So >" & ConvertDateToInt(dtpNgayHopDong.Value.ToShortDateString)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtDaXuat)
            For i As Integer = 0 To dtDaXuat.Rows.Count - 1
                sRet = sRet + Single.Parse(dtDaXuat.Rows(i)("SoLuong"))
            Next
            dbConn.Close()
        Catch ex As Exception

        End Try
    End Function
    Private Function SoLuongDaNhap(ByVal IdSanPham As Integer) As Single
        Try
            Dim sRet As Single = 0
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim query As String = ""
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dtDaXuat As New DataTable
            query = "Select SoLuongThu from ChiTietPhieuNhapKho Where IDSanPham=" & IdSanPham & "And NgayNhapTheoSo >" & ConvertDateToInt(dtpNgayHopDong.Value.ToShortDateString)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtDaXuat)
            For i As Integer = 0 To dtDaXuat.Rows.Count - 1
                sRet = sRet + Single.Parse(dtDaXuat.Rows(i)("SoLuongThu"))
            Next
            dbConn.Close()
        Catch ex As Exception

        End Try
    End Function
#End Region
#End Region

    Private Sub dtpNgayHopDong_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtpNgayHopDong.ValueChanged
        Try
            If dtpNgayHopDong.Value.ToShortDateString > Now.Date Then
                MessageBox.Show("Bạn không thể chọn ngày lớn hơn ngày hiện tại.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            If dtpNgayHopDong.Value.ToShortDateString = Now.Date Then
                SoLuongTonKho_Load_ByDateNow()
            Else
                SoLuongTonKho_Load_ByDate()
            End If
        Catch ex As Exception

        End Try
    End Sub

    Private Sub cmdInThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdInThongKe.Click
        Try
            frmInThongKeSoLuongTon.Show()
        Catch ex As Exception

        End Try
    End Sub

    Private Sub cemXuatExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cemXuatExcel.Click
        Try
            frmSaveFile.FileType = ".xls"
            ShowModalForm(frmSaveFile, False, False)
            Dim result As Boolean
            result = frmSaveFile.Result
            If result Then
                Dim Path As String = frmSaveFile.Path
                Dim FileName As String = frmSaveFile.FileName
                ExportExcel(Path, FileName & ".xls")
            End If
            frmSaveFile.txtPath.Text = ""
            frmSaveFile.txtFile.Text = ""
        Catch ex As Exception

        End Try
    End Sub
    Private Sub ExportExcel(ByVal Path As String, ByVal FileName As String)
        Try
            'Dim Start As DateTime = Now
            Dim SubTitle As String = "Ngày " & formatDateVN2String(Now.ToShortDateString)
            'Dim excelDoc As System.IO.StreamWriter = New System.IO.StreamWriter(FileName)
            Dim startExcelXML As String
            startExcelXML = "<?xml version=""1.0""?>" & vbCrLf & _
            "<?mso-application progid=""Excel.Sheet""?>" & vbCrLf & _
            "<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
            "xmlns:o =""urn:schemas-microsoft-com:office:office""" & vbCrLf & _
            "xmlns:x =""urn:schemas-microsoft-com:office:excel""" & vbCrLf & _
            "xmlns:ss =""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
             "xmlns:html=""http://www.w3.org/TR/REC-html40""" & ">" & vbCrLf & _
             "<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office""" & ">" & vbCrLf & _
              "<LastAuthor>NG0C TRAM</LastAuthor>" & vbCrLf & _
              "<Created>2011-04-04T13:47:34Z</Created>" & vbCrLf & _
              "<Version>11.9999</Version>" & vbCrLf & _
             "</DocumentProperties>" & vbCrLf & _
             "<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
              "<WindowHeight>10005</WindowHeight>" & vbCrLf & _
              "<WindowWidth>10005</WindowWidth>" & vbCrLf & _
              "<WindowTopX>120</WindowTopX>" & vbCrLf & _
              "<WindowTopY>135</WindowTopY>" & vbCrLf & _
              "<ProtectStructure>False</ProtectStructure>" & vbCrLf & _
              "<ProtectWindows>False</ProtectWindows>" & vbCrLf & _
             "</ExcelWorkbook>" & vbCrLf & _
             "<Styles>" & vbCrLf & _
              "<Style ss:ID=""Default""" & " ss:Name=""Normal""" & ">" & vbCrLf & _
               "<Alignment ss:Vertical=""Bottom""" & "/>" & vbCrLf & _
               "<Borders/>" & vbCrLf & _
               "<Font/>" & vbCrLf & _
               "<Interior/>" & vbCrLf & _
               "<NumberFormat/>" & vbCrLf & _
               "<Protection/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""s22""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "<Font x:Family=""Swiss""" & " ss:Size=""" & "16""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s24""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "<Font x:Family=""Swiss""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s27""" & ">" & vbCrLf & _
               "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
               "<Interior ss:Color=""" & "#99CCFF""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s29""" & ">" & vbCrLf & _
               "<Alignment ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
                "<Style ss:ID=""" & "s30""" & ">" & vbCrLf & _
                "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                "<Interior ss:Color=""" & "#CCFFCC""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s31""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s33""" & ">" & vbCrLf & _
               "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
               "</Style>" & vbCrLf & _
              "<Style ss:ID=""" & "s32""" & ">" & vbCrLf & _
              "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
              "</Style>" & vbCrLf & _
             "</Styles>" & vbCrLf & _
             "<Worksheet ss:Name=""" & "Sheet1""" & ">" & vbCrLf & _
              "<Table>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "98.25""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "99.75""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "149.25""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "101.25""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "106.5""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "72""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "50.25""" & "/>" & vbCrLf & _
               "<Column ss:Width=""" & "69""" & "/>" & vbCrLf & _
               "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "84.75""" & "/>" & vbCrLf & _
               "<Row ss:AutoFitHeight=""" & "0""" & " ss:Height=""" & "24""" & ">" & vbCrLf & _
                "<Cell ss:MergeAcross=""" & "5""" & " ss:StyleID=""" & "s22""" & "><Data ss:Type=""" & "String""" & ">BẢNG TỒN KHO THÀNH PHẨM</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
               "</Row>" & vbCrLf & _
               "<Row ss:Height=""" & "20.25""" & ">" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:MergeAcross=""" & "1""" & " ss:StyleID=""" & "s24""" & "><Data ss:Type=""" & "String""" & ">" & SubTitle & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
               "</Row>" & vbCrLf & _
               "<Row>" & vbCrLf & _
                "<Cell ss:Index=""" & "4""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:Index=""" & "9""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
               "</Row>" & vbCrLf & _
               "<Row ss:Index=""" & "6""" & ">" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">MÃ SẢN PHẦM</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">TÊN SẢN PHẨM</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">TỒN</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">MÃ SẢN PHẨM</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">TÊN SẢN PHẨM</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">TỒN</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
               "</Row>"
            'write
            ExportToFile(Path, FileName, startExcelXML)
            Dim contentExcelXML As String = ""
            'contentExcelXML = DevideRowGrid()
            Dim EndRow As Integer = Grid1.RowsCount - 1
            Dim StartRow As Integer = 0
            Dim K As Integer = EndRow \ 100
            Dim SoDu As Integer = EndRow Mod 100
            Dim aK(100) As Integer
            For i As Integer = 1 To 100
                If K * i <= EndRow Then
                    aK(i) = K * i
                End If
            Next
            aK(100) = 100 * K + SoDu
            Dim sRet As String = ""
            frmBusyLoading.Show()
            frmBusyLoading.progress.Minimum = 0
            frmBusyLoading.progress.Maximum = 100
            For i As Integer = 0 To 99
                sRet = ConvertExcel(aK(i) + 1, aK(i + 1))
                If sRet <> "" Then
                    ExportToFile(Path, FileName, sRet)
                End If
                frmBusyLoading.progress.Value = i
            Next
            frmBusyLoading.Close()
            'For i As Integer = 1 To Grid1.RowsCount - 1
            '    If Grid1(i, 0).ColumnSpan = 9 Then
            '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
            '                                "<Cell ss:MergeAcross=""" & "5""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 0).Value & "</Data></Cell>" & vbCrLf & _
            '                            "</Row>"
            '    Else
            '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 2).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 3).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & Grid1(i, 4).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 6).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 7).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & Grid1(i, 8).Value & "</Data></Cell>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
            '   "</Row>"
            '    End If
            '    frmBusyLoading.progress.Value = i
            'Next
            'frmBusyLoading.Close()
            Dim endExcelXML As String
            endExcelXML = "</Table>" & vbCrLf & _
              "<WorksheetOptions xmlns=""" & "urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
               "<Print>" & vbCrLf & _
                "<ValidPrinterInfo/>" & vbCrLf & _
                "<PaperSizeIndex>0</PaperSizeIndex>" & vbCrLf & _
                "<HorizontalResolution>600</HorizontalResolution>" & vbCrLf & _
                "<VerticalResolution>600</VerticalResolution>" & vbCrLf & _
               "</Print>" & vbCrLf & _
               "<Selected/>" & vbCrLf & _
               "<Panes>" & vbCrLf & _
                "<Pane>" & vbCrLf & _
                 "<Number>3</Number>" & vbCrLf & _
                 "<ActiveRow>1</ActiveRow>" & vbCrLf & _
                 "<ActiveCol>2</ActiveCol>" & vbCrLf & _
                 "<RangeSelection>R2C3:R2C4</RangeSelection>" & vbCrLf & _
                "</Pane>" & vbCrLf & _
               "</Panes>" & vbCrLf & _
               "<ProtectObjects>False</ProtectObjects>" & vbCrLf & _
               "<ProtectScenarios>False</ProtectScenarios>" & vbCrLf & _
              "</WorksheetOptions>" & vbCrLf & _
             "</Worksheet>" & vbCrLf & _
            "</Workbook>"
            ExportToFile(Path, FileName, endExcelXML)
            'Dim EndTime As DateTime = Now
            'MessageBox.Show((EndTime - Start).TotalSeconds())
            'excelDoc.Write(startExcelXML & contentExcelXML & endExcelXML)
            'excelDoc.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Function ConvertExcel(ByVal iBegin As Integer, ByVal iEnd As Integer) As String
        Try
            Dim contentExcelXML As String = ""
            For i As Integer = iBegin To iEnd
                If Grid1(i, 0).ColumnSpan = 9 Then
                    contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                                            "<Cell ss:MergeAcross=""" & "5""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 0).Value & "</Data></Cell>" & vbCrLf & _
                                        "</Row>"
                Else
                    contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 2).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 3).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & Grid1(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 6).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & Grid1(i, 7).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "Number""" & ">" & Grid1(i, 8).Value & "</Data></Cell>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
               "</Row>"
                End If
                'frmBusyLoading.progress.Value = i
            Next
            Return contentExcelXML
        Catch ex As Exception

        End Try
    End Function
End Class